package dtt;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Properties;
import java.sql.Statement;

import javax.mail.Message;
import javax.mail.Session;
import javax.mail.Transport;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeMessage;

public class DefectBO  {
	public boolean addDefect(DefectBean defect)
	{
	//	DefectBean outputbean = null;
		boolean flag = false;
		Connection con = DBConnection.getConnection();
		String template ="insert into t_defect (projectId,requirementId,testScriptId," +
				          "defectNbr,defectSeverity,defectPriority,defectReportedBy," +
				          "defectReproducibility,defectStatus,defectProductVerNbr," +
				          "defectBuildNbr,defectTgtVerNbr,defectDesc,defectStepstoReproduce," +
				          "defectDevFixCom)values" +
				                         "(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		try {
		PreparedStatement ps = con.prepareStatement(template);
		String projectId= defect.getProjectId();
		//String defectId = generateDefectId(projectId);
		String defectId = defect.getDefectId();
				
		ps.setInt(1,Integer.parseInt(projectId));
		System.out.println("project id:" + projectId);
		ps.setString(2, defect.getRequirementId());
		System.out.println("getRequirementId id:" + defect.getRequirementId());
		ps.setString(3,defect.getTestScriptId());
		ps.setString(4,defectId);
//		ps.setString(5,defect.getDefectDateSubmitted);
		ps.setString(5,defect.getDefectSeverity());
		ps.setString(6,defect.getDefectPriority());
		ps.setString(7,defect.getDefectReportedBy());
		ps.setString(8,defect.getDefectReproducibility());
		ps.setString(9,defect.getDefectStatus());
		ps.setString(10,defect.getDefectProductVerNbr());
		ps.setString(11,defect.getDefectBuildNbr());
		ps.setString(12,defect.getDefectTgtVerNbr());
		ps.setString(13,defect.getDefectDesc());
		ps.setString(14, defect.getDefectStepstoReproduce());
		ps.setString(15, defect.getDefectDevFixCom());
		
				
		int count = ps.executeUpdate();
		ps.close();
		
		
		DBConnection.releaseConnection(con);
		con = null;
		
		if(count>0){
			flag = true;
		}
		else{
			flag = false;
		}
		}
		catch(SQLException e){
		 System.out.println("WRONG HERE");
		 e.printStackTrace();
		}
		System.out.print("defectno is "+defect.getDefectId());
					
		return flag;
		
	}
	/**
	 * When a new defect is added,this method generates a new defectId based on number of defect already
	 * exsisiting in the given project
	 * @return
	 */
	public String generateDefectId(String projectId){
		String defectId;
		int count = 0;
		Connection con1 = DBConnection.getConnection();
		String template = "select count(*) from t_defect where projectId = ?";
		try{
			PreparedStatement ps1 = con1.prepareStatement(template);
			ps1.setString(1,projectId);
			ResultSet rs1 = ps1.executeQuery();
			rs1.next();
			count = rs1.getInt(1);
			} //end try
		catch(SQLException e){
		 e.printStackTrace();
		}// end catch
		count +=1;
		defectId = "" + count; 
		
		return defectId;
	}//end method generateDefectId
	/**
	 * 
	 * @param projectNo
	 * @param defectNo
	 * @return
	 * @throws SQLException
	 */
	
	public DefectBean getDefectDetail(String projectNo, String defectNo) throws SQLException {
		
		Connection con = DBConnection.getConnection();
		DefectBean defectbean = new DefectBean();
		defectbean.setDefectId(defectNo);
		defectbean.setDefectDesc("hello i do not know why");
		String template = "select projectId,requirementId, testScriptId,defectStatus,defectSeverity,defectPriority, " +
		"defectReportedBy,defectReproducibility ,defectProductVerNbr, defectBuildNbr, defectTgtVerNbr, defectDesc, defectStepstoReproduce ,defectDevFixCom" +  
		" from t_defect where defectNbr= ? and projectId= ? ";
		PreparedStatement ps = con.prepareStatement(template);
		System.out.println("DefectBO.getDefectDetail: projectId = "+projectNo);
		System.out.println("DefectBO.getDefectDetail: defectId = "+defectNo);
		try{
		ps.setString(1,defectNo);
		ps.setString(2,projectNo);
		//Statement ps = con.createStatement();
		ResultSet rs = ps.executeQuery();
		while(rs.next()){
			defectbean.setProjectId(String.valueOf(rs.getInt("projectId")));
			defectbean.setRequirementId(rs.getString("requirementId"));
			defectbean.setTestScriptId(rs.getString("testScriptId"));
			defectbean.setDefectStatus(rs.getString("defectStatus"));
			defectbean.setDefectSeverity(rs.getString("defectSeverity"));
			defectbean.setDefectPriority(rs.getString("defectPriority"));
			defectbean.setDefectReportedBy(rs.getString("defectReportedBy"));
			defectbean.setDefectReproducibility(rs.getString("defectReproducibility"));
			defectbean.setDefectProductVerNbr(rs.getString("defectProductVerNbr"));
			defectbean.setDefectBuildNbr(rs.getString("defectBuildNbr"));
			defectbean.setDefectTgtVerNbr(rs.getString("defectTgtVerNbr"));
			defectbean.setDefectDesc(rs.getString("defectDesc"));
			defectbean.setDefectStepstoReproduce(rs.getString("defectStepstoReproduce"));
			defectbean.setDefectDevFixCom(rs.getString("defectDevFixCom"));
			
			System.out.println("defect number is"+defectbean.getDefectId());
			System.out.println("project number is "+defectbean.getProjectId());
		}
		//ArrayList validFutureStates = BusinessRulesBO.getValidStateTransitions("Assessed","DEV");
		//defectbean.setValidFutureStates(validFutureStates);
		}
		catch(Exception e){
			e.printStackTrace();
		}
		System.out.println("Done");
		ps.close();
		DBConnection.releaseConnection(con);
		con = null;
		return defectbean;
	}
	
	public DefectBean updateDefect(DefectBean defectbean)
	{
		DefectBean outputbean = null;
		Connection con = DBConnection.getConnection();
		String template ="update t_defect set requirementId=?,testScriptId=?,defectSeverity=?," +
		                 "defectPriority=?,defectAssignedTo=?,defectReproducibility=?,defectStatus=?," +
		                 "defectProductVerNbr=?,defectBuildNbr=?,defectTgtVerNbr=?,defectFixedinVerNbr=?," +
		                 "defectDesc=?,defectStepstoReproduce=?,defectDevFixCom=? " +
		                 "where defectId=? and projectId=?";
		try {
		PreparedStatement ps = con.prepareStatement(template);
		ps.setString(1, defectbean.getRequirementId());
		ps.setString(2, defectbean.getTestScriptId());
		ps.setString(3, defectbean.getDefectSeverity());
		ps.setString(4, defectbean.getDefectPriority());
		//ps.setString(5,defectbean.getDefectLastUpdatedBy());
		ps.setString(5, defectbean.getDefectAssignedto());
		ps.setString(6, defectbean.getDefectReproducibility());
		ps.setString(7, defectbean.getDefectStatus());
		ps.setString(8,defectbean.getDefectProductVerNbr());
		ps.setString(9,defectbean.getDefectBuildNbr());
		ps.setString(10,defectbean.getDefectTgtVerNbr());
		ps.setString(11, defectbean.getDefectFixedinVerNbr());
		ps.setString(12,defectbean.getDefectDesc());
		ps.setString(13,defectbean.getDefectStepstoReproduce());
		ps.setString(14,defectbean.getDefectDevFixCom());
		ps.setString(15,defectbean.getDefectId());
		ps.setString(16,defectbean.getProjectId());
		
		
			
		int count=ps.executeUpdate();
		ps.close();

		DBConnection.releaseConnection(con);
		con = null;
		
		
			//
			System.out.println("Count is "+count);
			outputbean = getDefectDetail(defectbean.getProjectId(), defectbean.getDefectId());
		
		}
		catch(SQLException e){
		 System.out.println("WRONG HERE");
		 e.printStackTrace();
		}
		
		return outputbean;
		
	}
	
	@SuppressWarnings("unchecked")
	public ArrayList getDefectList(String projectId){
			//ProjectBean project = new ProjectBean();
			ArrayList defectList = new ArrayList();
			
			String template ="select * from t_defect where projectId=?";
			try{
				Connection con = DBConnection.getConnection();
				PreparedStatement ps = con.prepareStatement(template);
				System.out.println("project id in getdefectlist is "+projectId);
				ps.setInt(1,Integer.parseInt(projectId));
				ResultSet rs = ps.executeQuery();
				while(rs.next()){
				 DefectBean defect = new DefectBean();	
				 defect.setDefectId((rs.getString(2)));
				 defect.setProjectId(rs.getString(19));
				 defect.setDefectDesc(rs.getString(24));
				 defect.setDefectSeverity(rs.getString(4));
				 defect.setDefectStatus(rs.getString("defectStatus"));
				 defectList.add(defect);
				 
				}//end while
			ps.close();
			con = null;
			}//end try
			catch(SQLException e){
				e.printStackTrace();
			}
		
			return defectList;
	}
	public void sendMail(String toAddr, String subject, String body, String fromAddr){  
		try{  
		Properties props = new Properties();  
		props.put("mail.smtp.host","smtp.sbcglobal.yahoo.com");  
//		Here we specify the SMTP server through  
//		which the mail should be delivered  
	    String protocol = "smtp";
	    props.put("mail." + protocol + ".auth", "true");

		Session session = Session.getDefaultInstance(props, null);  
		Message msg = new MimeMessage(session);  
		msg.setFrom(new InternetAddress(fromAddr));  
//		Specify the From Address  
		InternetAddress[] tos =InternetAddress.parse(toAddr);  
//		Specify the To Address  
		msg.setRecipients(Message.RecipientType.TO,tos);  
		msg.setSubject(subject);  
//		Specify the Subject  
		msg.setText(body);  
//		Specify the Body  
		//Transport.send(msg);
	    Transport t = session.getTransport(protocol);
	    try {
	        t.connect("mail.luc.edu","hzhang9", "ztt750715");
	        t.sendMessage(msg, msg.getAllRecipients());
	    } finally {
	        t.close();
	    }
		System.out.println("Message is Sent");  
		}  
		catch(Exception e){  
		System.out.println(e);  
		}  
		}  
	public 	ArrayList searchDefect(SearchCriteria criteria){ 

	ArrayList defectList = 	new ArrayList(); 

	boolean first_param = true; 
	String template =

	"select * from t_defect "; 

	try{ 
	Connection con = DBConnection.getConnection();



	if(criteria.getProjectId() != null) { 
	template = template + 

	" where projectId="+criteria.getProjectId()+"" ; 
	first_param = 

	false; 
	}

//	end project id 

	if(criteria.getDefectId() != null) { 

	if(first_param) { 
	template = template + 

	" where defectId="+criteria.getDefectId()+"" ; 
	first_param=

	false; 
	} 

	else { 
	template = template + 

	" and defectId="+criteria.getDefectId()+"" ; 
	}

	}

//	end defect id 

	if(criteria.getReportedBy() != null) { 

	if(first_param) { 
	template = template + 

	" where defectReportedBy='"+criteria.getReportedBy()+"'" ; 
	first_param=

	false; 
	} 

	else { 
	template = template + 

	" and defectReportedBy='"+criteria.getReportedBy()+"'" ; 
	}

	}

//	end reported by 

	if(criteria.getSeverity() != null) { 

	if(first_param) { 
	template = template + 

	" where defectSeverity='"+criteria.getSeverity()+"'" ; 
	first_param=

	false; 
	} 

	else { 
	template = template + 

	" and defectSeverity='"+criteria.getSeverity()+"'" ; 
	}

	}

//	end severity 

	if(criteria.getDefectStatus() != null) { 

	if(first_param) { 
	template = template + 

	" where defectStatus='"+criteria.getDefectStatus()+"'" ; 
	first_param=

	false; 
	} 

	else { 
	template = template + 

	" and defectStatus='"+criteria.getDefectStatus()+"'" ; 
	}

	}

//	end status 

	if(criteria.getPriority() != null) { 

	if(first_param) { 
	template = template + 

	" where defectPriority='"+criteria.getPriority()+"'" ; 
	first_param=

	false; 
	} 

	else { 
	template = template + 

	" and defectPriority='"+criteria.getPriority()+"'" ; 
	}

	}

//	end priority 

	if(criteria.getAssignedTo() != null) { 

	if(first_param) { 
	template = template + 

	" where defectAssignedTo='"+criteria.getAssignedTo()+"'" ; 
	first_param=

	false; 
	} 

	else { 
	template = template + 

	" and defectAssignedTo='"+criteria.getAssignedTo()+"'" ; 
	}

	}

//	end assignedTo 

	if(criteria.getDescription() != null) { 

	if(first_param) { 
	template = template + 

	" where defectDesc like'%"+criteria.getDescription()+"%'" ; 
	first_param=

	false; 
	} 

	else { 
	template = template + 

	" and defectDesc like '%"+criteria.getDescription()+"%'" ; 
	}

	}

//	end description 

	if(criteria.getDateSubmitted() != null) { 

	if(first_param) { 

//	uncomment this for access 

template = template + " where defectDateSubmitted = to_date('"+criteria.getDateSubmitted()+"','MM/DD/YYYY')"; 
	/*template = template + 

	" where defectDateSubmitted =#"+criteria.getDateSubmitted()+"#" ; */ 
	first_param=

	false; 
	} 

	else { 

//	uncomment this for oracle 

//	template = template + " and defectDateSubmitted = to_date('"+criteria.getDateSubmitted()+"','MM/DD/YYYY')"; 
	template = template + 

	" and defectDateSubmitted =#"+criteria.getDateSubmitted()+"#" ; 
	}

	}

//	end description 
	System.

	out.println("Query is: "+template); 
	Statement ps = con.createStatement();

	System.

	out.println("project id in search defect is "+criteria.getProjectId()); 
	ResultSet rs = ps.executeQuery(template);


	while(rs.next()){ 
	DefectBean defect = getDefectDetail(rs.getString("projectId"),""+rs.getInt("defectNbr"));
	//defect.setDefectId(rs.getInt("defectid"));

	//defect.setProjectId(rs.getString(2));

	//defect.setDefectDesc(rs.getString(3));

	//defect.setDefectSeverity(rs.getString(4));

	//defect.setDefectStatus(rs.getString(5));

	defectList.add(defect);


	}

//	end while 
	ps.close();

	con = 

	null; 
	}

//	end try 

	catch(SQLException e){ 
	e.printStackTrace();

	}



	return defectList; 
	}

//	end search defect 
	}



